package com.hongdee.scm.accountOfFinance.service.impl;


import com.gexin.fastjson.JSONArray;
import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.scm.accountOfFinance.dao.ITScmAccountOfFinanceDao;
import com.hongdee.scm.accountOfFinance.entity.TScmAccountOfFinance;
import com.hongdee.scm.accountOfFinance.service.ITScmAccountOfFinanceService;
import com.hongdee.scm.countforms.entity.TScmStockForms;
import com.hongdee.scm.pushMessage.entity.TScmMapToJSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class TScmAccountOfFinanceServiceImpl implements ITScmAccountOfFinanceService{

    @Autowired
    private ITScmAccountOfFinanceDao itScmAccountOfFinanceDao;
    @Autowired
    private IHibernateDao iHibernateDao;

    /**
     * 往数据表里添加数据
     * @param uid
     * @throws Exception
     */
    @Override
    public void insertToTable(String uid) throws Exception{

        List<Map<String,Object>> list  = this.insertOldData();
        //将老系统的数据导入到数据表中
        for(Map<String,Object> data:list){
            TScmAccountOfFinance tScmAccountOfFinance = new TScmAccountOfFinance();
            tScmAccountOfFinance.setCreateTime(System.currentTimeMillis());
            tScmAccountOfFinance.setQueryDate(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
            if(data.get("companyId")!=null&&!"".equals(data.get("companyId"))){
              tScmAccountOfFinance.setCompanyId(data.get("companyId").toString());
            }
            if(data.get("oldFinanceIn")!=null&&!"".equals(data.get("oldFinanceIn"))){
              tScmAccountOfFinance.setOldFinanceIn(data.get("oldFinanceIn").toString());
            }
            if(data.get("oldFinanceOut")!=null&&!"".equals(data.get("oldFinanceOut"))){
               tScmAccountOfFinance.setOldFinanceOut(data.get("oldFinanceOut").toString());
            }
            if(data.get("oldFinanceStock")!=null&&!"".equals(data.get("oldFinanceStock"))){
               tScmAccountOfFinance.setOldFinanceStock(data.get("oldFinanceStock").toString());
            }
            //新系统是否月结
            Map map = this.itScmAccountOfFinanceDao.info("select * from t_scm_monthlybalance where company_id=? " +
                    "and monthly_balance_status ='0' order by monthly_balance_month desc limit 1",data.get("companyId"));
            if(map.size()>0){
                tScmAccountOfFinance.setMonthlyBalance(map.get("monthlyBalanceMonth").toString());
            }
            tScmAccountOfFinance.setNewFinanceIn("0");
            tScmAccountOfFinance.setNewFinanceOut("0");
            tScmAccountOfFinance.setNewFinanceStock("0");
            tScmAccountOfFinance.setNewCarIn("0");
            tScmAccountOfFinance.setNewCarOut("0");
            tScmAccountOfFinance.setNewCarStock("0");
            this.itScmAccountOfFinanceDao.saveOrUpdate(tScmAccountOfFinance);
        }

        //将新系统的数据导入到数据表中
        String sql = "select func_newOldAccount() ";
        List<String> params = new ArrayList<String>();
        this.itScmAccountOfFinanceDao.list(sql,params.toArray());
    }

    /**
     * 从url获取数据
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> insertOldData() throws Exception{

        List<Map<String,Object>> dataList = new ArrayList<>();
        StringBuffer resultBuffer = null;
        // 构建请求参数
        BufferedReader br = null;
        String res=null;
        try {
            //获取公司id列表
            List<Map<String,Object>> companyList = this.companyList();
            for(Map<String,Object> company:companyList){

                if("0".equals(company.get("status"))){//不取数
                    Map maps = new HashMap();
                    maps.put("oldFinanceIn","0");
                    maps.put("oldFinanceOut","0");
                    maps.put("oldFinanceStock","0");
                    maps.put("companyId",company.get("companyId"));
                    dataList.add(maps);
                }

                if("1".equals(company.get("status"))){//取数
                    String address = "";
                    address  = "http://10.26.61.220:8089/WebService.asmx/List_all?ip=" + company.get("ipAddr")+"&user="+company.get("userName")+
                            "&password="+company.get("password")+"&useid="+company.get("databaseAddr")+"&bmnum="+company.get("bmnum");
                    URL url = new URL(address);
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    //设置超时间为10秒
                    conn.setConnectTimeout(10 * 1000);
                    //防止屏蔽程序抓取而返回403错误
                    conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
                    //得到输入流
                    try{
                        InputStream inputStream = conn.getInputStream();
                        res = readInputStream(inputStream);
                        if(res.length()!=0){
                            Map<String,Object> map =  TScmMapToJSON.json2Map(res);
                            JSONArray jsonArray = (JSONArray) map.get("list");
                            Map mm = TScmMapToJSON.json2Map(jsonArray.get(0).toString());
                            Map maps = new HashMap();
                            maps.put("oldFinanceIn",mm.get("enter"));
                            maps.put("oldFinanceOut",mm.get("out"));
                            maps.put("oldFinanceStock",mm.get("stork"));
                            maps.put("companyId",company.get("companyId"));
                            dataList.add(maps);
                        }
                    }catch (Exception e){
                        continue;
                    }
                }

            }
        }catch (Exception e){
          throw new RuntimeException(e);
        }finally {
          if (br != null) {
              try {
                  br.close();
              } catch (IOException e) {
                  br = null;
                  throw new RuntimeException(e);
              }
          }
        }
        return dataList;
    }

    /**
     * 公司列表
     * @return
     * @throws Exception
     */
    private List<Map<String,Object>> companyList() throws Exception{

        List<Map<String,Object>> list= this.itScmAccountOfFinanceDao.list("select * from t_scm_company_match order by address");
        return list;
    }

    public static String readInputStream(InputStream inputStream) throws IOException {
            byte[] buffer = new byte[1024];
            int len = 0;
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            while((len = inputStream.read(buffer)) != -1) {
                bos.write(buffer, 0, len);
            }
            bos.close();
            System.out.println(new String(bos.toByteArray(),"utf-8"));
            return new String(bos.toByteArray(),"utf-8");
        }

    @Override
    public Map queryData(Map data,String start,String limit) throws Exception{

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select f.*,o.o_short_name from t_scm_account_finance f " +
                "inner join t_bc_organ o on f.company_id = o.id where 1=1");
        if(data.get("dateStart")!=null&&!"".equals(data.get("dateStart"))){
            sql.append(" and f.query_date>=?");
            params.add(data.get("dateStart"));
        }
        if(data.get("dateEnd")!=null&&!"".equals(data.get("dateEnd"))){
            sql.append(" and f.query_date<=?");
            params.add(data.get("dateEnd"));
        }

        sql.append(" order by f.query_date desc");

       Map map =  this.itScmAccountOfFinanceDao.page(sql.toString(),params.toArray(),start,limit);
       return map;
    }

    /**
     * 更新润华汽车库存报表
     * @throws Exception
     */
    @Override
    public void insertToForm() throws Exception{

        //当前时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        String queryMonth = sdf.format(new Date());

        String sql = "select * from t_scm_stock_form where query_month=? and company_id =? and brand_name=?";
        //获取数据
       List<Map<String,Object>> list = this.getForm();
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            params.add(queryMonth);
            params.add(map.get("companyId"));
            params.add(map.get("brandName"));
            List ll = this.itScmAccountOfFinanceDao.list(sql,params.toArray());
            TScmStockForms stockForms = new TScmStockForms();
            //新增
            if(ll==null||ll.size()==0){
                stockForms.setQueryMonth(queryMonth);
                stockForms.setBrandName(String.valueOf(map.get("brandName").toString()));
                stockForms.setCompanyId(String.valueOf(map.get("companyId").toString()));
                stockForms.setCompanyName(String.valueOf(map.get("companyName").toString()));
            }else{//修改
                String id = ((Map)ll.get(0)).get("id").toString();
                TScmStockForms tScmStockForm = (TScmStockForms) this.iHibernateDao.findEntity(TScmStockForms.class,id);
                stockForms.setId(tScmStockForm.getId());
                stockForms.setQueryMonth(tScmStockForm.getQueryMonth());
                stockForms.setBrandName(tScmStockForm.getBrandName());
                stockForms.setCompanyId(tScmStockForm.getCompanyId());
                stockForms.setCompanyName(tScmStockForm.getCompanyName());
            }
            stockForms.setStockPrice(String.valueOf(map.get("stockPrice").toString()));
            stockForms.setLastPrice(String.valueOf(map.get("lastPrice").toString()));
            stockForms.setNowCount(String.valueOf(map.get("nowCount").toString()));
            stockForms.setNowPrice(String.valueOf(map.get("nowPrice").toString()));
            stockForms.setOverCount(String.valueOf(map.get("overCount").toString()));
            stockForms.setOverPrice(String.valueOf(map.get("overPrice").toString()));
            stockForms.setMoreCount(String.valueOf(map.get("moreCount").toString()));
            stockForms.setMorePrice(String.valueOf(map.get("morePrice").toString()));
            stockForms.setLastYearPrice(String.valueOf(map.get("lastYearPrice").toString()));
            stockForms.setTurnDay(String.valueOf(map.get("turnDay").toString()));
            stockForms.setLastTurnDays(String.valueOf(map.get("lastTurnDays").toString()));
            stockForms.setNowYearCost(String.valueOf(map.get("nowYearCost").toString()));
            this.itScmAccountOfFinanceDao.saveOrUpdate(stockForms);
        }

    }

    private List getForm() throws Exception{
        StringBuffer sql = new StringBuffer();
        sql.append("select * from (select if(rowno is not null,tt.company_id,'合计') company_id,if(rowno is not null,tt.brand_name,'合计') brandName,if(rowno is not null,company_name,'合计') companyName,lastPrice," +
                " nowCount,nowPrice,overCount,overPrice,moreCount,morePrice,lastYearPrice,stockPrice,nowYearCost,turnDay,lastTurnDays from( " +
                " select temp.brand_name,temp.company_name,temp.company_id,round(sum(temp.lastPrice)/10000,2) lastPrice,SUM(temp.nowCount) nowCount,round(SUM(temp.nowPrice)/10000,2) nowPrice," +
                " SUM(temp.overCount) overCount,round(SUM(temp.overPrice)/10000,2) overPrice,SUM(temp.moreCount) moreCount,round(SUM(temp.morePrice)/10000,2) morePrice,round(SUM(temp.lastYearPrice)/10000,2) lastYearPrice," +
                " round(SUM(temp.stockPrice)/10000,2) stockPrice,round(SUM(temp.nowYearCost)/10000,2) nowYearCost,round(sum(temp.turnDay)/10000,2) turnDay,round(SUM(temp.lastTurnDays)/10000,2) lastTurnDays,(@rowNO := @rowNo+1) AS rowno from " +
                "  ( select t.brand_name,t.company_name,t.company_id,ifnull(d.lastPrice,0) lastPrice,ifnull(f.nowCount,0) nowCount," +
                " ifnull(f.nowPrice,0) nowPrice,ifnull(h.overCount,0) overCount,ifnull(h.overPrice,0.0) overPrice," +
                " ifnull(j.moreCount,0) moreCount,ifnull(j.morePrice,0) morePrice,ifnull(l.lastYearPrice,0) lastYearPrice," +
                " ifnull(func_monthStockPrice(t.company_id),0) stockPrice,ifnull(p.nowYearCost,0) nowYearCost," +
                " ifnull(round(func_monthStockPrice(t.company_id)/p.nowYearCost*365,2),0) turnDay," +
                " ifnull(round(func_lastYearTurnDays(t.company_id),2),0) lastTurnDays from (" +
                " select g.o_name brand_name,o.o_name company_name,c.company_id from t_scm_car c" +
                " inner join t_bc_organ o on o.id = c.company_id inner join t_bc_organ g on g.id = o.o_p_id" +
                " GROUP BY c.company_id ) t left join (" +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) lastPrice" +
                " from t_scm_car c where id in ( select car_id from t_scm_enter_stock_record where enter_date <=last_day(date_sub(now(),interval 1 month)))" +
                " and id not in ( select car_id from t_scm_outwarehouse where out_date<=last_day(date_sub(now(),interval 1 month)) and out_status ='1')" +
                " GROUP BY c.company_id ) d on t.company_id = d.company_id left join (" +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) nowPrice," +
                " count(c.id) as nowCount from t_scm_car c where id in (" +
                " select car_id from t_scm_enter_stock_record where enter_date <=last_day(curdate())) and id not in (" +
                " select car_id from t_scm_outwarehouse where out_date<=last_day(curdate()) and out_status ='1')" +
                " GROUP BY c.company_id ) f on t.company_id = f.company_id left join (" +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) overPrice," +
                " count(c.id) as overCount from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                " where TIMESTAMPDIFF(DAY,DATE_FORMAT(if(c.enter_warehouse_date_start is not null,c.enter_warehouse_date_start,s.enter_date),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d'))>180" +
                " GROUP BY c.company_id ) h on t.company_id = h.company_id left join (" +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) morePrice," +
                " count(c.id) as moreCount from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                " where TIMESTAMPDIFF(DAY,DATE_FORMAT(if(c.enter_warehouse_date_start is not null,c.enter_warehouse_date_start,s.enter_date),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d'))>180" +
                " GROUP BY c.company_id ) j on t.company_id = j.company_id left join (" +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) lastYearPrice" +
                " from t_scm_car c where id in (" +
                " select car_id from t_scm_enter_stock_record where enter_date <=last_day(date_sub(now(),interval 1 month)))" +
                " and id not in (" +
                " select car_id from t_scm_outwarehouse where out_date<=last_day(date_sub(now(),interval 1 month)) and out_status ='1')" +
                " GROUP BY c.company_id ) l on t.company_id = l.company_id left join ( " +
                " select c.company_id,round(sum(if(c.invoice_price is not null,invoice_price,estimate_price)),2) nowYearCost" +
                " from t_scm_car c where id in ( " +
                " select car_id from t_scm_outwarehouse where out_date<= concat(YEAR(now()),'-12-31') and out_status ='1')" +
                " GROUP BY c.company_id ) p on t.company_id = p.company_id ) temp,(select @rowNO:=0 ) r group by rowno with ROLLUP" +
                " ) tt ORDER BY tt.rowno desc,tt.brand_name desc ) t ");

        List list = this.itScmAccountOfFinanceDao.list(sql.toString());
        return list;
    }
}

